﻿using KPIS.GERP.GAMS.APP.App_UI.Commons;
using KPIS.GERP.GAMS.DAL;
using KPIS.GERP.GAMS.MODEL;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace KPIS.GERP.GAMS.APP.App_UI.Forms
{
    public partial class Import : System.Web.UI.Page
    {
        Main main = new Main();
        D_ACM_GENERAL_Ledger gl = new D_ACM_GENERAL_Ledger();

        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void cmdImport_Click(object sender, EventArgs e)
        {
            try
            {
                DataTable dtb = GetDataTableFromExcel();
                int nTranSeq = string.IsNullOrEmpty(txtTranSeq.Text) ? -1 : Convert.ToInt32(txtTranSeq.Text);

                foreach (DataRow dr in dtb.Rows)
                {
                    int? nAccSeq = main.ConvertNullInt(dr["pk"]);

                    if (nAccSeq.HasValue)
                    {
                        bool bDr = (main.ConvertNullDouble(dr["yokpaiamt1"]) ?? 0) > 0;
                        double dAmount = bDr ? (main.ConvertNullDouble(dr["yokpaiamt1"]) ?? 0) : (main.ConvertNullDouble(dr["yokpaiamt2"]) ?? 0);

                        if (dAmount > 0)
                        {
                            var model = new M_ACM_GENERAL_Ledger();
                            model.tran_seq = nTranSeq;
                            model.account_seq = nAccSeq;
                            model.dr_or_cr = bDr ? "Dr" : "Cr";
                            model.amount = dAmount;
                            model.record_status = "W";
                            model.created_by = Convert.ToInt32(Session["EmpSeq"]);
                            model.created_when = DateTime.Now;

                            gl.Insert_DB_CR(model);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataTableFromExcel()
        {
            using (var pck = new OfficeOpenXml.ExcelPackage())
            {
                using (var stream = FileUpload.PostedFile.InputStream)
                {
                    pck.Load(stream);
                }

                var ws = pck.Workbook.Worksheets.First();
                DataTable dtb = new DataTable();
                bool hasHeader = true;

                foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
                {
                    dtb.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
                }

                var startRow = hasHeader ? 2 : 1;
                var endRow = 134;//ws.Dimension.End.Row

                for (var rowNum = startRow; rowNum <= endRow; rowNum++)
                {
                    var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
                    var row = dtb.NewRow();
                    foreach (var cell in wsRow)
                    {
                        row[cell.Start.Column - 1] = cell.Text;
                    }
                    dtb.Rows.Add(row);
                }

                return dtb;
            }
        }
    }
}